﻿using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.ProviderBase;
using System.Data.Common;
using System.Data.SqlClient;

namespace Code
{
    public class BasketDataAccess
    {
        protected DataProvider _provider = DataProvider.GlobalDataProvider;

        public int getCurrentBasketID(int memID)
        {
            string strSQL = string.Format("select gh.id " +
                "from GIO_HANG as gh " +
                "where gh.idNguoiDung = {0} and gh.DangSuDung = 1 ",
                memID);
            object obj = _provider.ExecuteScalar(strSQL);
            if (obj ==  null)
                return -1;
            return Int32.Parse(obj.ToString());
        }

        public DataTable getListBasket(int memID)
        {
            string strSQL = string.Format(
                "select " +
                "gh.id as MaSoGio, " +
                "gh.NgayLapGio, "+
                "gh.NgayCapNhatGio, "+
                "gh.DangSuDung, "+
                "(	case "+
                "when not exists "+
                "(select * from CHI_TIET_GIO_HANG as ctgh where ctgh.idGioHang = gh.id) "+
                "then 0 "+
                "else (select SUM(ctgh.DonGia*ctgh.SoLuong) "+
                "from CHI_TIET_GIO_HANG as ctgh "+
                "where ctgh.idGioHang = gh.id) "+
                "end "+
                ") as TongTien " +
                "from GIO_HANG as gh " +
                "where gh.idNguoiDung = {0} " +
                "group by gh.id, gh.NgayLapGio, gh.NgayCapNhatGio, gh.DangSuDung "+
                "order by gh.NgayCapNhatGio desc",
                memID
                );
            return _provider.ExecuteQueryDataTable(strSQL);
        }

        public DataTable getBasketDetail(int bID)
        {
            string strSQL = string.Format(
                "select ctgh.id as idChiTietGiohang, ctgh.idGioHang, " +
                "sp.id as idSanPham, " +
                "ha.id as idHinhAnh, " +
                "sp.TenSanPham, " +
                "ctgh.SoLuong, " +
                "ctgh.DonGia, " +
                "ctgh.DonGia*ctgh.SoLuong as ThanhTien, "+
                "ctgh.NgayCapNhat, " +
                "ha.Url, " +
                "ha.MauSac, " +
                "CanCapNhatDonGia = " +
                "CASE " +
                "WHEN ctgh.DonGia = sp.Gia THEN 0 " +
                "ELSE 1 " +
                "END, " +
                "SoLuongVuotQua = "+
                "CASE "+
                "WHEN ctgh.SoLuong <= ha.SoLuongTon THEN 0 "+
                "ELSE 1 "+
                "END "+
                "from (CHI_TIET_GIO_HANG as ctgh join SAN_PHAM as sp on ctgh.idSanPham = sp.id) " +
                "join HINH_ANH as ha on ctgh.idHinhAnh = ha.id " +
                "where ctgh.idGioHang = {0} " +
                "order by ctgh.NgayCapNhat desc"
                ,bID
                );
            return _provider.ExecuteQueryDataTable(strSQL);
        }

        public void setCurrentBasketID(int bID)
        {
            string strSQL = string.Format("update GIO_HANG " +
                "set DangSuDung = " +
                "CASE " +
                "WHEN id = {0} THEN 1 " +
                "ELSE 0 " +
                "END " +
                "where idNguoiDung in (select idNguoiDung from GIO_HANG where id = {0})", bID);
            _provider.ExecuteNonQuery(strSQL);
        }

        public void addNewBasket(int memID)
        {
            string strSQL = string.Format(
                "insert into GIO_HANG(idNguoiDung, NgayLapGio, NgayCapNhatGio, DangSuDung) values ({0}, @NgayLapGio, @NgayCapNhatGio, 0)", memID);
            
            DbCommand command = _provider.CreateCommand();

            DbParameter paramNgayLapGio = _provider.CreateParameter();
            paramNgayLapGio.DbType = DbType.DateTime;
            paramNgayLapGio.ParameterName = "@NgayLapGio";
            paramNgayLapGio.Value = DateTime.Now;

            DbParameter paramNgayCapNhatGio = _provider.CreateParameter();
            paramNgayCapNhatGio.DbType = DbType.DateTime;
            paramNgayCapNhatGio.ParameterName = "@NgayCapNhatGio";
            paramNgayCapNhatGio.Value = DateTime.Now;

            command.CommandText = strSQL;
            command.CommandType = CommandType.Text;
            command.Parameters.Add(paramNgayLapGio);
            command.Parameters.Add(paramNgayCapNhatGio);

            _provider.ExecuteNonQuery(command, true);


            strSQL = string.Format(
                "select MAX(id) " +
                "from GIO_HANG " +
                "where idNguoiDung = {0} "
                , memID);

            try
            {
                object obj = _provider.ExecuteScalar(strSQL);
                if (obj != null)
                {
                    int bID = Int32.Parse(obj.ToString());
                    setCurrentBasketID(bID);
                }
            }
            catch (Exception)
            {
            }
        }

        public void updatePrice(int bID)
        {
            string strSQL = string.Format(
                "update CHI_TIET_GIO_HANG " +
                "set DonGia = ( select sp.Gia " +
                "from SAN_PHAM as sp " +
                "where sp.id = idSanPham " +
                ") " +
                "where idGioHang = {0} ", bID
                );
            _provider.ExecuteNonQuery(strSQL, true);
        }

        public void deleteBasket(int bID, int memID)
        {
            DbConnection cnn = _provider.Connection;
            DbTransaction transaction;
            if (cnn.State != ConnectionState.Open)
                cnn.Open();
            transaction = cnn.BeginTransaction();
            try 
            {
                DbCommand com1 = _provider.CreateCommand();
                com1.CommandText = string.Format("delete CHI_TIET_GIO_HANG where idGioHang = {0}", bID);
                com1.CommandType = CommandType.Text;
                com1.Connection = cnn;
                com1.Transaction = transaction;

                com1.ExecuteNonQuery();

                DbCommand com2 = _provider.CreateCommand();
                com2.CommandText = string.Format("delete GIO_HANG where id = {0}", bID); ;
                com2.CommandType = CommandType.Text;
                com2.Connection = cnn;
                com2.Transaction = transaction;

                com2.ExecuteNonQuery();

                DbCommand com3 = _provider.CreateCommand();
                com3.CommandText = string.Format(
                    "update GIO_HANG " +
                    "set DangSuDung = 1 " +
                    "where idNguoiDung = {0} and not exists ( " +
                    "select * " +
                    "from GIO_HANG " +
                    "where idNguoiDung = {0} and DangSuDung = 1 " +
                    ") and id = ( " +
                    "select MAX(id) " +
                    "from GIO_HANG " +
                    "where idNguoiDung = {0} )",
                    memID);
                com3.CommandType = CommandType.Text;
                com3.Connection = cnn;
                com3.Transaction = transaction;

                com3.ExecuteNonQuery();

                transaction.Commit();
            } 
            catch (Exception sqlError) {
                transaction.Rollback();
            }
            finally
            {
                if (cnn.State == ConnectionState.Open)
                    cnn.Close();
            }
        }

        public void removeFromBasket(int bID, int bdID)
        {
            DbConnection cnn = _provider.Connection;
            DbTransaction transaction;
            if (cnn.State != ConnectionState.Open)
                cnn.Open();
            transaction = cnn.BeginTransaction();
            try
            {
                DbCommand com1 = _provider.CreateCommand();
                com1.CommandText = string.Format("delete CHI_TIET_GIO_HANG where id = {0}", bdID);
                com1.CommandType = CommandType.Text;
                com1.Connection = cnn;
                com1.Transaction = transaction;

                com1.ExecuteNonQuery();

                DbCommand com2 = _provider.CreateCommand();
                com2.CommandText = string.Format("update GIO_HANG set NgayCapNhatGio=@NgayCapNhatGio where id = {0}", bID);
                com2.CommandType = CommandType.Text;
                com2.Connection = cnn;
                com2.Transaction = transaction;
                DbParameter paramNgayCapNhatGio = _provider.CreateParameter();
                paramNgayCapNhatGio.DbType = DbType.DateTime;
                paramNgayCapNhatGio.ParameterName = "@NgayCapNhatGio";
                paramNgayCapNhatGio.Value = DateTime.Now;
                com2.Parameters.Add(paramNgayCapNhatGio);

                com2.ExecuteNonQuery();

                transaction.Commit();
            }
            catch (Exception)
            {
                transaction.Rollback();
            }
            finally
            {
                if (cnn.State == ConnectionState.Open)
                    cnn.Close();
            }
        }

        public void updateProductQuan(int bdID, int quan)
        {
            string strSQL = string.Format(
                "update CHI_TIET_GIO_HANG " +
                "set SoLuong = {0} " +
                "where id = {1} ", quan, bdID
                );
            _provider.ExecuteNonQuery(strSQL, true);
        }

        public int getMaxQuantity(int imgID)
        {
            string strSQL = string.Format(
                "select SoLuongTon " +
                "from HINH_ANH " +
                "where id={0}", imgID
                );

            object obj = _provider.ExecuteScalar(strSQL);
            if (obj == null)
                return 0;
            int mQuan;
            if (Int32.TryParse(obj.ToString(), out mQuan))
            {
                return mQuan;
            }
            return 0;
        }

        public void addToBasket(int basketID, int productID, int colorID, int quantity)
        {
            int Price = getPriceOfProduct(productID);
            int basketDetailID;
            string strSQL;
            basketDetailID = getBasketDetailOfSameProduct(basketID, productID, colorID);

            // no basket detail for same product
            if (basketDetailID == -1)
            {
                basketDetailID = getMaxIDBasketDetail();
                strSQL = string.Format(
                    "insert into CHI_TIET_GIO_HANG(id, idSanPham, idHinhAnh, SoLuong, DonGia, idGioHang, NgayCapNhat) " +
                    "values({0}, {1}, {2}, {3}, {4}, {5}, @NgayCapNhat)",
                    basketDetailID,
                    productID,
                    colorID,
                    quantity,
                    Price,
                    basketID
                    );
            }
            else
            {
                strSQL = string.Format(
                    "update CHI_TIET_GIO_HANG " +
                    "set DonGia = {0}, " +
                    "SoLuong = SoLuong + {1}, " +
                    "NgayCapNhat = @NgayCapNhat " +
                    "where id = {2} ",
                    Price,
                    quantity,
                    basketDetailID
                    );
            }

            DbConnection cnn = _provider.Connection;
            DbTransaction transaction;
            if (cnn.State != ConnectionState.Open)
                cnn.Open();
            transaction = cnn.BeginTransaction();
            try
            {
                DbCommand com1 = _provider.CreateCommand();
                com1.CommandText = strSQL;
                com1.CommandType = CommandType.Text;
                com1.Connection = cnn;
                com1.Transaction = transaction;

                DbParameter paramNgayCapNhatGio = _provider.CreateParameter();
                paramNgayCapNhatGio.DbType = DbType.DateTime;
                paramNgayCapNhatGio.ParameterName = "@NgayCapNhat";
                paramNgayCapNhatGio.Value = DateTime.Now;
                com1.Parameters.Add(paramNgayCapNhatGio);

                com1.ExecuteNonQuery();
                
                transaction.Commit();
            }
            catch (Exception)
            {
                transaction.Rollback();
            }
            finally
            {
                if (cnn.State == ConnectionState.Open)
                    cnn.Close();
            }
        }

        public int getBasketDetailOfSameProduct(int bID, int pID, int cID)
        {
            string strSQL = string.Format(
                "select id " + 
                "from CHI_TIET_GIO_HANG " +
                "where idGioHang = {0} and idSanPham = {1} and idHinhAnh = {2}",
                bID,
                pID,
                cID
                );

            object obj = _provider.ExecuteScalar(strSQL);
            if (obj == null)
                return -1;
            int rsl;
            if (Int32.TryParse(obj.ToString(), out rsl))
            {
                return rsl;
            }
            return -1;
        }

        public int getMaxIDBasketDetail()
        {
            string strSQL = "select MAX(id)+1 from CHI_TIET_GIO_HANG";
            object obj = _provider.ExecuteScalar(strSQL);
            if (obj == null)
                return 1;
            int rsl;
            if (Int32.TryParse(obj.ToString(), out rsl))
            {
                return rsl;
            }
            return 1;
        }

        public int getPriceOfProduct(int pID)
        {
            string strSQL = string.Format(
                "select Gia from SAN_PHAM where id = {0}",
                pID
                );
            object obj = _provider.ExecuteScalar(strSQL);
            if (obj == null)
                return -1;
            int rsl;
            if (Int32.TryParse(obj.ToString(), out rsl))
            {
                return rsl;
            }
            return -1;
        }

        public bool checkAllPriceUpdated(int bID)
        {
            string strSQL = string.Format(
                "select  SUM( " +
                "CASE " +
                "WHEN ctgh.DonGia = sp.Gia THEN 0 " +
                "ELSE 1 " +
                "END ) as SoLuongChuaUpdate " +
                "from (CHI_TIET_GIO_HANG as ctgh join SAN_PHAM as sp on ctgh.idSanPham = sp.id) " +
                "join HINH_ANH as ha on ctgh.idHinhAnh = ha.id " +
                "where ctgh.idGioHang = {0} ",
                bID
                );
            object obj = _provider.ExecuteScalar(strSQL);
            if (obj == null)
                return true;
            int rsl;
            if (Int32.TryParse(obj.ToString(), out rsl))
            {
                if (rsl == 0)
                    return true;
                return false;
            }
            return false;
        }

        public bool checkAppropriateQuantity(int bID)
        {
            string strSQL = string.Format(
                "select  SUM( " +
                "CASE " +
                "WHEN ctgh.SoLuong <= ha.SoLuongTon THEN 0 " +
                "ELSE 1 " +
                "END ) as SoLuongChuaUpdate " +
                "from (CHI_TIET_GIO_HANG as ctgh join SAN_PHAM as sp on ctgh.idSanPham = sp.id) " +
                "join HINH_ANH as ha on ctgh.idHinhAnh = ha.id " +
                "where ctgh.idGioHang = {0} ",
                bID
                );
            object obj = _provider.ExecuteScalar(strSQL);
            if (obj == null)
                return true;
            int rsl;
            if (Int32.TryParse(obj.ToString(), out rsl))
            {
                if (rsl == 0)
                    return true;
                return false;
            }
            return false;
        }

        public bool checkBasketEmpty(int bID)
        {
            string strSQL = string.Format(
                "select count(id) from CHI_TIET_GIO_HANG where idGioHang = {0}",
                bID
                );
            object obj = _provider.ExecuteScalar(strSQL);
            if (obj == null)
                return true;
            int rsl;
            if (Int32.TryParse(obj.ToString(), out rsl))
            {
                if (rsl == 0)
                    return true;
                return false;
            }
            return false;
        }

        public bool checkValidBasket(int mID, int bID)
        {
            string strSQL = string.Format(
                "select count(id) from GIO_HANG where id = {0} and idNguoiDung = {1}",
                bID, mID
                );

            object obj = _provider.ExecuteScalar(strSQL);
            if (obj == null)
                return false;
            int rsl;
            if (Int32.TryParse(obj.ToString(), out rsl))
            {
                if (rsl == 0)
                    return false;
                return true;
            }
            return false;
        }

        public DataTable getPTTT()
        {
            string strSQL = string.Format(
                "select id, TenPhuongThuc from PT_THANH_TOAN"
                );
            return _provider.ExecuteQueryDataTable(strSQL);
        }

        public DataTable getPTGH()
        {
            string strSQL = string.Format(
                "select id, TenPhuongThuc from PT_GIAO_HANG"
                );
            return _provider.ExecuteQueryDataTable(strSQL);
        }

        public DataTable getBank()
        {
            string strSQL = string.Format(
                "select * from NGAN_HANG"
                );
            return _provider.ExecuteQueryDataTable(strSQL);
        }

        public int getTotalPrice(int bID)
        {
            string strSQL = string.Format(
                "select SUM(SoLuong*DonGia) as TotalPrice " +
                "from CHI_TIET_GIO_HANG " +
                "where idGioHang = {0} ",
                bID
                );

            object obj = _provider.ExecuteScalar(strSQL);
            if (obj == null)
                return 0;
            int rsl;
            if (Int32.TryParse(obj.ToString(), out rsl))
            {
                return rsl;
            }
            return 0;
        }

        public int payBasket(
            int memID,
            int basketID,
            int totalPrice,
            int ptgh,
            int pttt,
            string sTenNguoiNhan,
            string sDiaChiNguoiNhan,
            string sSoDTNguoiNhan,
            string sSoTaiKhoanCongTy,
            string sTenNganHang)
        {
            DbConnection cnn = _provider.Connection;
            DbTransaction transaction;
            if (cnn.State != ConnectionState.Open)
                cnn.Open();
            transaction = cnn.BeginTransaction();
            try
            {
                // insert to hoa don
                DbCommand com1 = _provider.CreateCommand();
                com1.CommandText = string.Format(
                    "insert into HOA_DON(NgayDatHang, TongTien, NguoiDatHang, idPTGH, idPTTT, TenNguoiNhan, DiaChiNguoiNhan, SDTNguoiNhan) " +
                    "values (@NgayDatHang, {0}, {1}, {2}, {3}, N'{4}', N'{5}', '{6}')",
                    totalPrice,
                    memID,
                    ptgh,
                    pttt,
                    sTenNguoiNhan,
                    sDiaChiNguoiNhan,
                    sSoDTNguoiNhan);
                com1.CommandType = CommandType.Text;
                com1.Connection = cnn;
                com1.Transaction = transaction;

                DbParameter paramNgayDatHang = _provider.CreateParameter();
                paramNgayDatHang.DbType = DbType.DateTime;
                paramNgayDatHang.ParameterName = "@NgayDatHang";
                paramNgayDatHang.Value = DateTime.Now;
                com1.Parameters.Add(paramNgayDatHang);

                com1.ExecuteNonQuery();

                

                transaction.Commit();
            }
            catch (Exception)
            {
                transaction.Rollback();
            }
            finally
            {
                if (cnn.State == ConnectionState.Open)
                    cnn.Close();
            }

            int orderID = getOrderID(
                    totalPrice,
                    memID,
                    ptgh,
                    pttt,
                    sTenNguoiNhan,
                    sDiaChiNguoiNhan,
                    sSoDTNguoiNhan);
            if (orderID == -1)
            {
                return -1;
            }
            
            cnn = _provider.Connection;
            DbTransaction transaction2;
            if (cnn.State != ConnectionState.Open)
                cnn.Open();
            transaction2 = cnn.BeginTransaction();

            try
            {
                

                DbCommand com2 = _provider.CreateCommand();
                com2.CommandText = string.Format(
                    "insert into CHI_TIET_HOA_DON(idSanPham, idHinhAnh, SoLuong, DonGia, idHoaDon) " +
                    "select ctgh.idSanPham, ctgh.idHinhAnh, ctgh.SoLuong, ctgh.DonGia, {0} " +
                    "from CHI_TIET_GIO_HANG as ctgh " +
                    "where ctgh.idGioHang = {1} ",
                    orderID, basketID
                    );
                com2.CommandType = CommandType.Text;
                com2.Connection = cnn;
                com2.Transaction = transaction2;
                com2.ExecuteNonQuery();

                transaction2.Commit();
            }
            catch (Exception)
            {
                transaction2.Rollback();
            }
            finally
            {
                if (cnn.State == ConnectionState.Open)
                    cnn.Close();
            }

            return orderID;
        }

        public int getOrderID(int totalPrice, int memID, int ptgh, int pttt, string sTenNguoiNhan, string sDiaChiNguoiNhan, string sSoDTNguoiNhan)
        {
            string strSQL = string.Format(
                "select max(hd.id) " +
                "from HOA_DON as hd " +
                "where hd.NguoiDatHang = {0} " +
                "and hd.TongTien = {1} " +
                "and hd.idPTGH = {2} " +
                "and hd.idPTTT = {3} " +
                "and hd.SDTNguoiNhan = '{4}'",
                memID,
                totalPrice,
                ptgh,
                pttt,
                sSoDTNguoiNhan
                );

            object obj = _provider.ExecuteScalar(strSQL);
            if (obj == null)
                return -1;
            int rsl;
            if (Int32.TryParse(obj.ToString(), out rsl))
            {
                return rsl;
            }
            return -1;
        }

        public DataTable getOrderDetail(int oID)
        {
            string strSQL = string.Format(
                "select cthd.idSanPham, SoLuong, DonGia, TenSanPham, MauSac, Url, SoLuong*DonGia as ThanhTien " +
                "from CHI_TIET_HOA_DON cthd join  " +
                "( SAN_PHAM sp join HINH_ANH ha " +
                "on sp.id = ha.idSanPham ) " +
                "on cthd.idSanPham = sp.id " +
                "where idHoaDon = {0} ",
                oID
                );
            return _provider.ExecuteQueryDataTable(strSQL);
        }

        public void getOrderInfo(int oID, out string sHoTenNguoiNhan, out string sDiaChiNguoiNhan, out string sSoDTNguoiNhan, out string sTongTien, out string sNgayGiaoDich)
        {
            string strSQL = string.Format(
                "select * from HOA_DON where id = {0}",
                oID
                );
            DataTable dt = _provider.ExecuteQueryDataTable(strSQL);
            sHoTenNguoiNhan = dt.Rows[0]["TenNguoiNhan"].ToString();
            sDiaChiNguoiNhan = dt.Rows[0]["DiaChiNguoiNhan"].ToString();
            sSoDTNguoiNhan = dt.Rows[0]["SDTNguoiNhan"].ToString();
            sTongTien = dt.Rows[0]["TongTien"].ToString();
            sNgayGiaoDich = dt.Rows[0]["NgayDatHang"].ToString();
        }
    }
}